const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🧹 清理备份表并重新开始实例表更新');
console.log('===================================');

try {
  // 1. 查找所有备份表
  const backupTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE '%_backup_%'
    ORDER BY name
  `).all();
  
  console.log(`📊 找到 ${backupTables.length} 个备份表`);
  
  // 2. 删除所有备份表
  console.log('🗑️ 删除备份表...');
  let deletedCount = 0;
  for (const table of backupTables) {
    try {
      db.prepare(`DROP TABLE IF EXISTS [${table.name}]`).run();
      deletedCount++;
      if (deletedCount % 10 === 0) {
        console.log(`  已删除 ${deletedCount}/${backupTables.length} 个备份表`);
      }
    } catch (error) {
      console.log(`  ❌ 删除 ${table.name} 失败: ${error.message}`);
    }
  }
  console.log(`✅ 成功删除 ${deletedCount} 个备份表`);
  
  // 3. 查找原始实例表
  const originalTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    AND name NOT LIKE '%_backup_%'
    AND name != 'device_instances_piping_cooling_water_spray_system'
    ORDER BY name
  `).all();
  
  console.log(`📋 剩余原始实例表: ${originalTables.length} 个`);
  
  // 4. 重新开始更新流程
  console.log('\\n🔧 开始更新原始实例表');
  console.log('============================');
  
  // 获取设备类型映射
  const deviceMappings = db.prepare(`
    SELECT classification_name, specialty, naming_table, position_table, base_params_table
    FROM device_type_mappings
    ORDER BY specialty, classification_name
  `).all();
  
  // 获取三个标准属性表的字段结构
  const namingFields = db.prepare(`PRAGMA table_info(table_c2)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  const positionFields = db.prepare(`PRAGMA table_info(table_c4)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  const positionFields2 = db.prepare(`PRAGMA table_info(table_c6)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  console.log(`📋 标准字段结构:`);
  console.log(`  命名表字段 (table_c2): ${namingFields.length}个`);
  console.log(`  位置表字段 (table_c4): ${positionFields.length}个`);
  console.log(`  位置表字段 (table_c6): ${positionFields2.length}个`);
  
  let successCount = 0;
  let failCount = 0;
  
  // 只处理前5个表进行测试
  const testTables = originalTables.slice(0, 5);
  console.log(`\\n🧪 测试更新前 ${testTables.length} 个表...`);
  
  for (const table of testTables) {
    console.log(`\\n🔧 更新实例表: ${table.name}`);
    console.log('='.repeat(50));
    
    try {
      // 从表名推断设备类型和专业
      const tableNameParts = table.name.replace('device_instances_', '').split('_');
      const specialty = tableNameParts[0];
      
      // 专业名称映射（英文->中文）
      const specialtyMapping = {
        'piping': '配管专业',
        'electrical': '电气专业', 
        'hvac': '暖通专业',
        'communication': '通信专业',
        'instrumentation': '仪表专业'
      };
      
      const chineseSpecialty = specialtyMapping[specialty];
      
      // 查找对应的设备类型映射
      let deviceMapping = null;
      if (chineseSpecialty) {
        const specialtyMappings = deviceMappings.filter(m => m.specialty === chineseSpecialty);
        if (specialtyMappings.length > 0) {
          deviceMapping = specialtyMappings[0];
        }
      }
      
      if (!deviceMapping) {
        console.log(`  ❌ 未找到 ${specialty} 专业的设备类型映射，跳过`);
        failCount++;
        continue;
      }
      
      console.log(`  ✅ 使用设备映射: ${deviceMapping.classification_name}`);
      
      // 获取基础参数表字段
      let baseParamsFields = [];
      try {
        baseParamsFields = db.prepare(`PRAGMA table_info(${deviceMapping.base_params_table})`).all()
          .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
      } catch (error) {
        console.log(`  ⚠️  无法获取基础参数表，使用默认字段`);
        baseParamsFields = [
          {name: '规格', type: 'TEXT'},
          {name: '型号', type: 'TEXT'},
          {name: '制造厂家', type: 'TEXT'}
        ];
      }
      
      // 获取当前表的数据
      const oldData = db.prepare(`SELECT * FROM [${table.name}]`).all();
      console.log(`  💾 当前数据: ${oldData.length} 条记录`);
      
      // 创建备份表
      const backupTableName = `${table.name}_backup_${Date.now()}`;
      db.prepare(`CREATE TABLE [${backupTableName}] AS SELECT * FROM [${table.name}]`).run();
      
      // 删除原表
      db.prepare(`DROP TABLE [${table.name}]`).run();
      
      // 生成新的表结构
      const newColumns = [];
      
      // 添加业务字段
      newColumns.push('device_type_code TEXT');
      newColumns.push('instance_number TEXT');
      newColumns.push('object_number TEXT');
      newColumns.push('object_description TEXT');
      
      // 添加命名表字段（naming_前缀）
      namingFields.forEach(field => {
        const fieldName = `naming_${field.name}`;
        newColumns.push(`[${fieldName}] ${field.type}`);
      });
      
      // 添加位置表字段（position_前缀）
      const positionTable = deviceMapping.position_table;
      if (positionTable === 'table_c4') {
        positionFields.forEach(field => {
          const fieldName = `position_${field.name}`;
          newColumns.push(`[${fieldName}] ${field.type}`);
        });
      } else if (positionTable === 'table_c6') {
        positionFields2.forEach(field => {
          const fieldName = `position_${field.name}`;
          newColumns.push(`[${fieldName}] ${field.type}`);
        });
      }
      
      // 添加基础参数表字段（base_params_前缀）
      baseParamsFields.forEach(field => {
        const fieldName = `base_params_${field.name}`;
        newColumns.push(`[${fieldName}] ${field.type}`);
      });
      
      // 创建新表
      const createTableSQL = `
        CREATE TABLE [${table.name}] (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          ${newColumns.join(',\\n          ')},
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
      `;
      
      db.prepare(createTableSQL).run();
      console.log(`  ✅ 重建表结构，总字段: ${newColumns.length + 3}个`);
      
      // 迁移数据（如果有的话）
      if (oldData.length > 0) {
        console.log(`  🔄 迁移 ${oldData.length} 条数据...`);
        const insertSQL = `
          INSERT INTO [${table.name}] (device_type_code, instance_number, object_number, object_description)
          SELECT device_type_code, instance_number, object_number, object_description 
          FROM [${backupTableName}]
        `;
        
        try {
          const result = db.prepare(insertSQL).run();
          console.log(`  ✅ 成功迁移 ${result.changes} 条记录`);
        } catch (migrateError) {
          console.log(`  ⚠️  数据迁移失败: ${migrateError.message}`);
        }
      }
      
      // 删除临时备份
      db.prepare(`DROP TABLE [${backupTableName}]`).run();
      
      successCount++;
      console.log(`  🎉 ${table.name} 更新完成`);
      
    } catch (error) {
      console.log(`  ❌ 更新 ${table.name} 失败: ${error.message}`);
      failCount++;
    }
  }
  
  // 总结
  console.log('\\n📊 测试更新结果');
  console.log('===================');
  console.log(`✅ 成功更新: ${successCount}个表`);
  console.log(`❌ 更新失败: ${failCount}个表`);
  
  if (successCount > 0) {
    console.log('\\n🎯 测试成功！可以继续更新剩余表');
    console.log(`剩余需要更新的表: ${originalTables.length - testTables.length}个`);
  }
  
} catch (error) {
  console.error('❌ 脚本执行失败:', error.message);
} finally {
  db.close();
}